package cn.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import cn.kpi.entity.pingyou.UserNdkhEntity;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.controller.AttBmHzController;
import cn.kpi.entity.attendance.AttSummaryEntity;
import cn.kpi.entity.usualassessment.UserUATMzpdSummaryEntity;

public class ExcelHandler {
    static Logger logger1 = Logger.getLogger(ExcelHandler.class);
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    public static Boolean writeExcelKq(List<AttSummaryEntity> list, String finalXlsxPath, String filename,String title, HttpServletResponse response) {
        OutputStream out = null;
        OutputStream ops = null;
        SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd");
        try {
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);

            Row rowtitle = sheet.getRow(0);
            if (rowtitle != null) {
                Cell cell = rowtitle.getCell(0);
                if (cell != null) {
                    AttSummaryEntity first = list.get(0);
                    cell.setCellValue(title);
                }
            }

            CellStyle cellStyle = createCellStyle(workBook);


            String orgName = "";
            int i =0;

            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < list.size(); j++) {
                AttSummaryEntity entity = list.get(j);
                String time ="";
                if(entity.getCreatetime()!=null) time = formattime.format(entity.getCreatetime());
                String orgrowName = entity.getOrgname();
                if(orgrowName == null) continue;

                Row row = sheet.createRow(j + 3+i);
                // 创建一行：从第二行开始，跳过属性列
                if(orgName.equals(orgrowName)) {
                    getRow(cellStyle, j, entity, row);
                }
                else {//插入一个合并行后，在插入一行
                    orgName = orgrowName;
                    if(entity.getAppusername() == null) entity.setAppusername("");
                    i++;//加一行
                    String rtitle = orgName+"   填报人："+entity.getByusername()+" 填报电话："+entity.getPhone()+" 填报时间:"+time+" 审批人："+entity.getAppusername();
                    createHeaderRow(workBook,row,sheet,rtitle,20);
                    row = sheet.createRow(j + 3+i);
                    getRow(cellStyle, j, entity, row);
                }

            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(ConfigOption.getInstance().getTemplateurl() + "/" + filename);
            workBook.write(out);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            logger1.info(e.getMessage());
            return false;
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
                if (ops != null) {
                    ops.flush();
                    ops.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    public static Boolean writeExcelKqSum(List<AttSummaryEntity> list, String finalXlsxPath, String filename,String title, HttpServletResponse response) {
        OutputStream out = null;
        OutputStream ops = null;
//        SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat formattime = new SimpleDateFormat("yyyy-MM-dd");
        try {
            // 获取总列数
            //int columnNumCount = cloumnCount;
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);

            Row rowtitle = sheet.getRow(0);
            if (rowtitle != null) {
                Cell cell = rowtitle.getCell(0);
                if (cell != null) {
                    AttSummaryEntity first = list.get(0);
                    cell.setCellValue(title);
                }
            }

            CellStyle cellStyle = createCellStyle(workBook);

            int i =0;

            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < list.size(); j++) {
                AttSummaryEntity entity = list.get(j);
                Row row = sheet.createRow(j+3);
                getsumRow(cellStyle, j, entity, row);
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(ConfigOption.getInstance().getTemplateurl() + "/" + filename);
            workBook.write(out);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            logger1.info(e.getMessage());
            return false;
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
                if (ops != null) {
                    ops.flush();
                    ops.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }


    private static void getRow(CellStyle cellStyle, int j, AttSummaryEntity entity, Row row) {
        // 得到要插入的每一条记录
        Cell cell = row.createCell(0);
        cell.setCellValue(j + 1);
        cell.setCellStyle(cellStyle);

        Cell cell1 = row.createCell(1);
        cell1.setCellValue(entity.getOrgname());
        cell1.setCellStyle(cellStyle);


        Cell cell111 = row.createCell(2);
        cell111.setCellValue(entity.getYear());
        cell111.setCellStyle(cellStyle);

        Cell cell112 = row.createCell(3);
        cell112.setCellValue(entity.getMouth());
        cell112.setCellStyle(cellStyle);


        Cell cell2 = row.createCell(4);
        cell2.setCellValue(entity.getCardno());
        cell2.setCellStyle(cellStyle);

        Cell cell3 = row.createCell(5);
        cell3.setCellValue(entity.getRealname());
        cell3.setCellStyle(cellStyle);

        Cell cell4 = row.createCell(6);
        cell4.setCellValue(entity.getSfjb() == 0 ? "否" : "是");
        cell4.setCellStyle(cellStyle);

        Cell cell6 = row.createCell(7);
        cell6.setCellValue(entity.getWcqtsxj());
        cell6.setCellStyle(cellStyle);

        Cell cell7 = row.createCell(8);
        cell7.setCellValue(entity.getXjts());
        cell7.setCellStyle(cellStyle);

        Cell cell8 = row.createCell(9);
        cell8.setCellValue(entity.getTqjts());
        cell8.setCellStyle(cellStyle);

        Cell cell9 = row.createCell(10);
        cell9.setCellValue(entity.getHjts());
        cell9.setCellStyle(cellStyle);

        Cell cell10 = row.createCell(11);
        cell10.setCellValue(entity.getSyjts());
        cell10.setCellStyle(cellStyle);

        Cell cell11 = row.createCell(12);
        cell11.setCellValue(entity.getSangjts());
        cell11.setCellStyle(cellStyle);

        Cell cell12 = row.createCell(13);
        cell12.setCellValue(entity.getGsts());
        cell12.setCellStyle(cellStyle);

        Cell cell13 = row.createCell(14);
        cell13.setCellValue(entity.getBjts());
        cell13.setCellStyle(cellStyle);

        Cell cell14 = row.createCell(15);
        cell14.setCellValue(entity.getSjts());
        cell14.setCellStyle(cellStyle);

        Cell cell15 = row.createCell(16);
        cell15.setCellValue(entity.getQtts());
        cell15.setCellStyle(cellStyle);

        Cell cell16 = row.createCell(17);
        cell16.setCellValue(entity.getCdcc());
        cell16.setCellStyle(cellStyle);

        Cell cell17 = row.createCell(18);
        cell17.setCellValue(entity.getZtcc());
        cell17.setCellStyle(cellStyle);

        Cell cell18 = row.createCell(19);
        cell18.setCellValue(entity.getKgcs());
        cell18.setCellStyle(cellStyle);

        Cell cell19 = row.createCell(20);
        cell19.setCellValue(entity.getRemark());
        cell19.setCellStyle(cellStyle);
    }

    private static void getsumRow(CellStyle cellStyle, int j, AttSummaryEntity entity, Row row) {
        // 得到要插入的每一条记录
        Cell cell = row.createCell(0);
        cell.setCellValue(j + 1);
        cell.setCellStyle(cellStyle);

        Cell cell1 = row.createCell(1);
        cell1.setCellValue(entity.getOrgname());
        cell1.setCellStyle(cellStyle);


        Cell cell2 = row.createCell(2);
        cell2.setCellValue(entity.getCardno());
        cell2.setCellStyle(cellStyle);

        Cell cell3 = row.createCell(3);
        cell3.setCellValue(entity.getRealname());
        cell3.setCellStyle(cellStyle);

        Cell cell4 = row.createCell(4);
        cell4.setCellValue(entity.getSfjb() == 0 ? "否" : "是");
        cell4.setCellStyle(cellStyle);

        Cell cell6 = row.createCell(5);
        cell6.setCellValue(entity.getWcqtsxj());
        cell6.setCellStyle(cellStyle);

        Cell cell7 = row.createCell(6);
        cell7.setCellValue(entity.getXjts());
        cell7.setCellStyle(cellStyle);

        Cell cell8 = row.createCell(7);
        cell8.setCellValue(entity.getTqjts());
        cell8.setCellStyle(cellStyle);

        Cell cell9 = row.createCell(8);
        cell9.setCellValue(entity.getHjts());
        cell9.setCellStyle(cellStyle);

        Cell cell10 = row.createCell(9);
        cell10.setCellValue(entity.getSyjts());
        cell10.setCellStyle(cellStyle);

        Cell cell11 = row.createCell(10);
        cell11.setCellValue(entity.getSangjts());
        cell11.setCellStyle(cellStyle);

        Cell cell12 = row.createCell(11);
        cell12.setCellValue(entity.getGsts());
        cell12.setCellStyle(cellStyle);

        Cell cell13 = row.createCell(12);
        cell13.setCellValue(entity.getBjts());
        cell13.setCellStyle(cellStyle);

        Cell cell14 = row.createCell(13);
        cell14.setCellValue(entity.getSjts());
        cell14.setCellStyle(cellStyle);

        Cell cell15 = row.createCell(14);
        cell15.setCellValue(entity.getQtts());
        cell15.setCellStyle(cellStyle);

        Cell cell16 = row.createCell(15);
        cell16.setCellValue(entity.getCdcc());
        cell16.setCellStyle(cellStyle);

        Cell cell17 = row.createCell(16);
        cell17.setCellValue(entity.getZtcc());
        cell17.setCellStyle(cellStyle);

        Cell cell18 = row.createCell(17);
        cell18.setCellValue(entity.getKgcs());
        cell18.setCellStyle(cellStyle);

        Cell cell19 = row.createCell(18);
        cell19.setCellValue(entity.getRemark());
        cell19.setCellStyle(cellStyle);
    }

    public static Boolean writeExcelNdpyjg(List<UserUATMzpdSummaryEntity> list, String finalXlsxPath, String filename, HttpServletResponse response, int year) {
        OutputStream out = null;
        OutputStream ops = null;
        try {
            // 获取总列数
            //int columnNumCount = cloumnCount;
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);
            Row rowtitle = sheet.getRow(0);
            if (rowtitle != null) {
                Cell cell = rowtitle.getCell(0);
                if (cell != null) {
                    UserUATMzpdSummaryEntity first = list.get(0);
                    cell.setCellValue(year + " 年度评优结果表");
                }
            }
            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < list.size(); j++) {
                UserUATMzpdSummaryEntity entity = list.get(j);
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 3);
                // 得到要插入的每一条记录
                Cell cell = row.createCell(0);
                cell.setCellValue(j + 1);

                Cell cell1 = row.createCell(1);
                cell1.setCellValue(entity.getRealname());


                Cell cell111 = row.createCell(2);
                cell111.setCellValue(entity.getCardno());

                Cell cell112 = row.createCell(3);
                cell112.setCellValue(entity.getOrgname());


                Cell cell2 = row.createCell(4);
                cell2.setCellValue(year);

                Cell cell3 = row.createCell(5);
                cell3.setCellValue(entity.getUserno());

                DecimalFormat df2 = new DecimalFormat("###.00");

                Cell cell4 = row.createCell(6);
                if (entity.getYzhdf() > 0) {
                    cell4.setCellValue(df2.format(entity.getYzhdf()));
                }
                Cell cell6 = row.createCell(7);
                cell6.setCellValue(entity.getYzzpdjg());

                Cell cell7 = row.createCell(8);
                cell7.setCellValue(entity.getYdymzpyjg());

                Cell cell8 = row.createCell(9);
                cell8.setCellValue(entity.getYszdwname());


                Cell cell9 = row.createCell(10);
                if (entity.getEzhdf() > 0) {
                    cell9.setCellValue(df2.format(entity.getEzhdf()));
                }
                Cell cell10 = row.createCell(11);
                cell10.setCellValue(entity.getEzzpdjg());

                Cell cell11 = row.createCell(12);
                cell11.setCellValue(entity.getEdymzpyjg());

                Cell cell12 = row.createCell(13);
                cell12.setCellValue(entity.getEszdwname());

                Cell cell13 = row.createCell(14);
                if (entity.getSzhdf() > 0) {
                    cell13.setCellValue(df2.format(entity.getSzhdf()));
                }
                Cell cell14 = row.createCell(15);
                cell14.setCellValue(entity.getSzzpdjg());

                Cell cell15 = row.createCell(16);
                cell15.setCellValue(entity.getSdymzpyjg());

                Cell cell16 = row.createCell(17);
                cell16.setCellValue(entity.getSszdwname());

                Cell cell17 = row.createCell(18);
                if (entity.getSizhdf() > 0) {
                    cell17.setCellValue(df2.format(entity.getSizhdf()));
                }
                Cell cell18 = row.createCell(19);
                cell18.setCellValue(entity.getSizzpdjg());

                Cell cell19 = row.createCell(20);
                cell19.setCellValue(entity.getSidymzpyjg());

                Cell cell20 = row.createCell(21);
                cell20.setCellValue(entity.getSiszdwname());

//	                Cell cell17 = row.createCell(18);
//	                cell17.setCellValue(entity.getZtcc());
//	                
//	                Cell cell18 = row.createCell(19);
//	                cell18.setCellValue(entity.getKgcs());
//	                
//	                Cell cell19 = row.createCell(20);
//	                cell19.setCellValue(entity.getRemark());

            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(ConfigOption.getInstance().getTemplateurl() + "/" + filename);
            workBook.write(out);

            //输出
//	            String codedFileName = java.net.URLEncoder.encode(filename, "UTF-8");     
//	            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//	            response.setCharacterEncoding("utf-8");
//	            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");  
//	            ops = response.getOutputStream(); 
//	            workBook.write(ops);  
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            logger1.info(e.getMessage());
            return false;
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
                if (ops != null) {
                    ops.flush();
                    ops.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith(EXCEL_XLS)) {     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }


    private static CellStyle createCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //TODO 使用新版本poi
//        style.setAlignment((short) 2);
//        style.setVerticalAlignment((short) 1);
//        style.setFillForegroundColor((short) 9);
//        Font font = workbook.createFont();
//        font.setColor((short) 8);
//        font.setFontHeightInPoints((short) 12);
//        style.setWrapText(true);
        //加上边框
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setBorderTop((short) 1);
        return style;
    }

    private static void createHeaderRow(Workbook workbook, Row row, Sheet sheet, String header,Integer mergedNum) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        cellStyle.setFont(font);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
        Cell cell = row.createCell(0);
        cell.setCellValue(header);
        cell.setCellStyle(cellStyle);
        CellRangeAddress region1 = new CellRangeAddress(row.getRowNum(), row.getRowNum(),  0, mergedNum);
        sheet.addMergedRegion(region1);
        // 使用RegionUtil类为合并后的单元格添加边框
        RegionUtil.setBorderBottom(1, region1, sheet,workbook); // 下边框
        RegionUtil.setBorderLeft(1, region1, sheet,workbook); // 左边框
        RegionUtil.setBorderRight(1, region1, sheet,workbook); // 有边框
        RegionUtil.setBorderTop(1, region1, sheet,workbook); // 上边框
    }

    public static Boolean writeExcelNdkhps(List<UserNdkhEntity> list, String finalXlsxPath, String filename, HttpServletResponse response, int year){
        OutputStream out = null;
        OutputStream ops = null;
        try {
            // 获取总列数
            //int columnNumCount = cloumnCount;
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);

            Row rowtitle=sheet.getRow(0);
            if(rowtitle!=null){
                Cell cell = rowtitle.getCell(0);
                if(cell!=null){
                    cell.setCellValue(year+" 年  度考核表");
                }
            }
            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < list.size(); j++) {
                UserNdkhEntity entity=list.get(j);
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 3);
                // 得到要插入的每一条记录
                Cell cell = row.createCell(0);
                cell.setCellValue(j+1);

                Cell cell1= row.createCell(1);
                cell1.setCellValue(entity.getRealname());


                Cell cell111= row.createCell(2);
                cell111.setCellValue(entity.getCardno());

                Cell cell112= row.createCell(3);
                cell112.setCellValue(entity.getOrgname());


                Cell cell2 = row.createCell(4);
                cell2.setCellValue(entity.getYzzpdjg());

                Cell cell3 = row.createCell(5);
                cell3.setCellValue(entity.getYszdwname());

                Cell cell4 = row.createCell(6);
                cell4.setCellValue(entity.getEzzpdjg());


                Cell cell6 = row.createCell(7);
                cell6.setCellValue(entity.getEszdwname());

                Cell cell7 = row.createCell(8);
                cell7.setCellValue(entity.getSzzpdjg());

                Cell cell8 = row.createCell(9);
                cell8.setCellValue(entity.getSszdwname());

                Cell cell9 = row.createCell(10);
                cell9.setCellValue(entity.getSizzpdjg());

                Cell cell10 = row.createCell(11);
                cell10.setCellValue(entity.getSiszdwname());

                Cell cell11 = row.createCell(12);
                cell11.setCellValue(entity.getRemark());

                Cell cell12 = row.createCell(13);
                cell12.setCellValue(entity.getNdkhpsjg());



            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out =  new FileOutputStream(ConfigOption.getInstance().getTemplateurl()+"/"+filename);
            workBook.write(out);

            //输出
//	            String codedFileName = java.net.URLEncoder.encode(filename, "UTF-8");
//	            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//	            response.setCharacterEncoding("utf-8");
//	            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
//	            ops = response.getOutputStream();
//	            workBook.write(ops);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            logger1.info(e.getMessage());
            return false;
        } finally{
            try {
                if(out != null){
                    out.flush();
                    out.close();
                }
                if(ops!=null){
                    ops.flush();
                    ops.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }
}
